package cn.ichiva.luckysheet.utils;

import cn.ichiva.luckysheet.enenty.Ct;
import cn.ichiva.luckysheet.enenty.Mc;
import cn.ichiva.luckysheet.enenty.Merge;
import cn.ichiva.luckysheet.enenty.V;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.jayway.jsonpath.Configuration;
import com.jayway.jsonpath.JsonPath;
import org.springframework.util.StringUtils;

import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

import static cn.ichiva.luckysheet.jdbc.jdbcUtil.jdbcUtils;

/**
 * TODO
 *
 * @author pzq
 * @version 1.0
 * @date 2021/12/8 11:12
 */
public class test {
    public static void mains(String[] args) throws SQLException, ClassNotFoundException {
        int xlast=0;
        int ylast=0;
        //获取数据
        String sql1 = "select * from student_info";
        List<Map<String, Object>> list=jdbcUtils(sql1);
        //y轴最后一个格子的坐标为 表头所占+数据填充个数
        ylast=list.size()+2;
        //需要替换内容的
        List<JSONObject> listdaichuli= new ArrayList<>();

        String json ="[{\"name\":\"Cell\",\"index\":\"sheet_01\",\"order\":0,\"status\":1,\"celldata\":[{\"r\":0,\"c\":0,\"v\":{\"v\":\"人员表1\",\"m\":\"人员表1\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"mc\":{\"r\":0,\"c\":0,\"rs\":1,\"cs\":4},\"ht\":\"0\"}},{\"r\":0,\"c\":1,\"v\":{\"mc\":{\"r\":0,\"c\":0},\"ht\":\"0\"}},{\"r\":0,\"c\":2,\"v\":{\"mc\":{\"r\":0,\"c\":0},\"ht\":\"0\"}},{\"r\":0,\"c\":3,\"v\":{\"mc\":{\"r\":0,\"c\":0},\"ht\":\"0\"}},{\"r\":1,\"c\":0,\"v\":{\"m\":\"姓名\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"姓名\"}},{\"r\":1,\"c\":1,\"v\":{\"m\":\"年级\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"年级\"}},{\"r\":1,\"c\":2,\"v\":{\"m\":\"分数\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"分数\"}},{\"r\":1,\"c\":3,\"v\":{\"m\":\"性别\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"性别\"}},{\"r\":2,\"c\":0,\"v\":{\"m\":\"select(name)\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"select(name)\"}},{\"r\":2,\"c\":1,\"v\":{\"m\":\"select(grade)\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"select(grade)\"}},{\"r\":2,\"c\":2,\"v\":{\"m\":\"select(fraction)\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"select(fraction)\"}},{\"r\":2,\"c\":3,\"v\":{\"m\":\"select(gender)\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"select(gender)\"}},{\"r\":2,\"c\":4,\"v\":{\"v\":\"2\",\"ct\":{\"fa\":\"General\",\"t\":\"n\"},\"m\":\"2\"}}],\"jfgird_select_save\":[],\"luckysheet_select_save\":[{\"row\":[3,3],\"column\":[4,4],\"row_focus\":3,\"column_focus\":4,\"moveXY\":{\"x\":3,\"y\":4},\"left\":296,\"width\":73,\"top\":60,\"height\":19,\"left_move\":296,\"width_move\":73,\"top_move\":60,\"height_move\":19}],\"config\":{\"merge\":{\"0_0\":{\"r\":0,\"c\":0,\"rs\":1,\"cs\":4}},\"borderInfo\":[{\"rangeType\":\"range\",\"borderType\":\"border-all\",\"color\":\"#000\",\"style\":\"1\",\"range\":[{\"left\":0,\"width\":295,\"top\":-1,\"height\":20,\"left_move\":0,\"width_move\":295,\"top_move\":0,\"height_move\":59,\"row\":[0,2],\"column\":[0,3],\"row_focus\":0,\"column_focus\":0}]}]},\"visibledatarow\":[20,40,60,80,100,120,140,160,180,200,220,240,260,280,300,320,340,360,380,400,420,440,460,480,500,520,540,560,580,600,620,640,660,680,700,720,740,760,780,800,820,840,860,880,900,920,940,960,980,1000,1020,1040,1060,1080,1100,1120,1140,1160,1180,1200,1220,1240,1260,1280,1300,1320,1340,1360,1380,1400,1420,1440,1460,1480,1500,1520,1540,1560,1580,1600,1620,1640,1660,1680],\"visibledatacolumn\":[74,148,222,296,370,444,518,592,666,740,814,888,962,1036,1110,1184,1258,1332,1406,1480,1554,1628,1702,1776,1850,1924,1998,2072,2146,2220,2294,2368,2442,2516,2590,2664,2738,2812,2886,2960,3034,3108,3182,3256,3330,3404,3478,3552,3626,3700,3774,3848,3922,3996,4070,4144,4218,4292,4366,4440],\"ch_width\":4560,\"rh_height\":1760,\"luckysheet_selection_range\":[],\"zoomRatio\":1},{\"name\":\"Data\",\"index\":\"sheet_02\",\"order\":1,\"status\":0,\"celldata\":[]},{\"name\":\"Picture\",\"index\":\"sheet_03\",\"order\":2,\"status\":0,\"celldata\":[]}]";
        JSONArray jsonArray = (JSONArray) JSONObject.parse(json);
        //如果只有一个sheet那就是get(0),有多个那就对应取下标
        JSONObject jsonObject = (JSONObject) jsonArray.get(0);
        JSONArray jsonObjectList = jsonObject.getJSONArray("celldata");
        for(int i=0;i<jsonObjectList.size();i++) {
            String v=jsonObjectList.getJSONObject(i).get("v").toString();
            JSONObject jsonObject1 =JSONObject.parseObject(v);
            if(ObjectUtils.isNotEmpty(jsonObject1.get("m"))){
                if(jsonObject1.get("m").toString().lastIndexOf("select")!= -1){
                    System.out.println(jsonObjectList.getJSONObject(i).get("r"));
                    System.out.println(jsonObjectList.getJSONObject(i).get("c"));
                    System.out.println(i+jsonObject1.get("m").toString());
                    JSONObject jsonn = new JSONObject();
                    V v1 = new V();
                    jsonn.put("r",jsonObjectList.getJSONObject(i).get("r"));
                    jsonn.put("c",jsonObjectList.getJSONObject(i).get("c"));
                    jsonn.put("v",jsonObject1);
                    v1.setM(jsonObject1.get("m").toString());
                    Ct ct = new Ct();
                    ct.setFa("General");
                    ct.setT("g");
                    v1.setCt(ct);
                    listdaichuli.add(jsonn);
                }
            }
        }
        JSONArray jsonObjectListn = new JSONArray();
        for (int i = 0; i < list.size(); i++) {
            list.get(i).get("name");//姓名
            list.get(i).get("grade");//年级
            list.get(i).get("fraction");//分数
            list.get(i).get("gender");//性别
            for (int j = 0; j < listdaichuli.size(); j++) {
                String v=listdaichuli.get(j).get("v").toString();
                JSONObject jsonObject1 =JSONObject.parseObject(v);
                String sel=jsonObject1.get("m").toString();
                String ghj=sel.substring(sel.indexOf("(")+1,sel.indexOf(")"));
                JSONObject json1 = new JSONObject();
                V v2 = new V();
                json1.put("r",i+2);
                json1.put("c",j);
                json1.put("v",v2);
                v2.setM(list.get(i).get(ghj).toString());
                v2.setV(list.get(i).get(ghj).toString());
                Ct ct = new Ct();
                ct.setFa("General");
                ct.setT("g");
                v2.setCt(ct);
                jsonObjectList.add(json1);
            }
        }
        //设置边框
        Configuration conf = Configuration.defaultConfiguration();
        String set = JsonPath.using(conf).parse(jsonObject).set("$['config']['borderInfo'][0]['range'][0]['column'][1]", 10).jsonString();

        JSONObject jsonObjectconfig = jsonObject.getJSONObject("config");
        String borderInfo=jsonObjectconfig.get("borderInfo").toString();
        JSONArray jsonObject1 =JSONArray.parseArray(borderInfo);

        JSONArray jsonArray1=(JSONArray) jsonObject1.getJSONObject(0).get("range");

        JSONArray jsonArray2=(JSONArray) jsonArray1.getJSONObject(0).get("column"); //x轴边框
        JSONArray jsonArray3=(JSONArray) jsonArray1.getJSONObject(0).get("row");//y轴边框

        Map<String, Object> jsonMap = JSONObject.toJavaObject(jsonArray1.getJSONObject(0), Map.class);
        Integer[] a = {0,xlast};
        String set123 = JsonPath.using(conf).parse(jsonObject).set("$['config']['borderInfo'][0]['range'][0]['column']", a).jsonString();
        jsonMap.put("column",a);
        Integer[] b = {0,ylast};
        jsonMap.put("row",b);
        String jsonty = JSON.toJSONString(jsonMap);//map转String
        JSONObject jsonObject2gg=jsonObject1.getJSONObject(0);
        jsonObject1.set(0,JSON.parseObject(jsonty));//String转json
        jsonObject.put("config",jsonObjectconfig);
//        JSONObject json1 = new JSONObject();
//        V v = new V();
//        json1.put("r","2");
//        json1.put("c","4");
//        json1.put("v",v);
//        v.setM("测试");
//        Ct ct = new Ct();
//        ct.setFa("General");
//        ct.setT("g");
//        v.setCt(ct);
//        jsonObjectList.add(json1);
        jsonObject.put("celldata",jsonObjectList);
        jsonArray.add(jsonObject);
        String g=jsonArray.toString();
        System.out.println(jsonArray);
    }

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        List<Map<String, Object>> listsum=new ArrayList<>();
        //需要替换内容的
        List<JSONObject> listdaichuli= new ArrayList<>();
        String str =  HttpRequest.sendGet("http://api.jeecg.com/mock/26/subtotal",null);
        JSONObject jsonObjectStr  = JSONObject.parseObject(str);
        List<Map<String,Object>> maps = (List<Map<String, Object>>) jsonObjectStr.get("data");
        String sql2="select * from test_user_extend";
        String sql3="select * from test_user_info";
        List<Map<String, Object>> list2=jdbcUtils(sql2);
        List<Map<String, Object>> list3=jdbcUtils(sql3);
        String json = "[{\"name\":\"Cell\",\"index\":\"sheet_01\",\"order\":0,\"status\":1,\"celldata\":[{\"r\":0,\"c\":0,\"v\":{\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"ht\":\"0\",\"m\":\"区域\",\"v\":\"区域\",\"mc\":{\"r\":0,\"c\":0,\"rs\":2,\"cs\":1}}},{\"r\":0,\"c\":1,\"v\":{\"ht\":\"0\",\"m\":\"省份\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"省份\",\"mc\":{\"r\":0,\"c\":1,\"rs\":2,\"cs\":1}}},{\"r\":0,\"c\":2,\"v\":{\"ht\":\"0\",\"m\":\"#groupRight(year)年\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"#groupRight(year)年\",\"mc\":{\"r\":0,\"c\":2,\"rs\":1,\"cs\":3}}},{\"r\":0,\"c\":3,\"v\":{\"mc\":{\"r\":0,\"c\":2}}},{\"r\":0,\"c\":4,\"v\":{\"mc\":{\"r\":0,\"c\":2}}},{\"r\":1,\"c\":0,\"v\":{\"mc\":{\"r\":0,\"c\":0}}},{\"r\":1,\"c\":1,\"v\":{\"mc\":{\"r\":0,\"c\":1}}},{\"r\":1,\"c\":2,\"v\":{\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"m\":\"单位\",\"v\":\"单位\"}},{\"r\":1,\"c\":3,\"v\":{\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"m\":\"金额\",\"v\":\"金额\"}},{\"r\":1,\"c\":4,\"v\":{\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"m\":\"小计\",\"v\":\"小计\"}},{\"r\":2,\"c\":0,\"v\":{\"m\":\"#groupt(areaname)\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"#groupt(areaname)\"}},{\"r\":2,\"c\":1,\"v\":{\"m\":\"#group(dept)\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"#group(dept)\"}},{\"r\":2,\"c\":2,\"v\":{\"m\":\"#select(price)\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"#select(price)\"}},{\"r\":2,\"c\":3,\"v\":{\"m\":\"#select(amount)\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"#select(amount)\"}},{\"r\":2,\"c\":4,\"v\":{\"m\":\"#select(price)\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"#select(price)\"}}],\"jfgird_select_save\":[],\"luckysheet_select_save\":[{\"left\":436,\"width\":135,\"top\":120,\"height\":19,\"left_move\":436,\"width_move\":135,\"top_move\":120,\"height_move\":19,\"row\":[6,6],\"column\":[3,3],\"row_focus\":6,\"column_focus\":3}],\"config\":{\"merge\":{\"0_0\":{\"r\":0,\"c\":0,\"rs\":2,\"cs\":1},\"0_1\":{\"r\":0,\"c\":1,\"rs\":2,\"cs\":1},\"0_2\":{\"r\":0,\"c\":2,\"rs\":1,\"cs\":3}},\"borderInfo\":[{\"rangeType\":\"range\",\"borderType\":\"border-all\",\"style\":\"1\",\"color\":\"#000\",\"range\":[{\"row\":[0,1],\"column\":[0,3]}]},{\"rangeType\":\"range\",\"borderType\":\"border-all\",\"style\":\"1\",\"color\":\"#000\",\"range\":[{\"row\":[0,1],\"column\":[0,3]}]},{\"rangeType\":\"range\",\"borderType\":\"border-all\",\"style\":\"1\",\"color\":\"#000\",\"range\":[{\"row\":[0,1],\"column\":[0,3]}]},{\"rangeType\":\"range\",\"borderType\":\"border-none\",\"style\":\"1\",\"color\":\"#000\",\"range\":[{\"row\":[0,1],\"column\":[0,3]}]}],\"columnlen\":{\"0\":149,\"1\":126,\"2\":158,\"3\":135,\"4\":131,\"5\":141},\"customWidth\":{\"0\":1,\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1},\"rowlen\":{},\"rowhidden\":{}},\"visibledatarow\":[20,40,60,80,100,120,140,160,180,200,220,240,260,280,300,320,340,360,380,400,420,440,460,480,500,520,540,560,580,600,620,640,660,680,700,720,740,760,780,800,820,840,860,880,900,920,940,960,980,1000,1020,1040,1060,1080,1100,1120,1140,1160,1180,1200,1220,1240,1260,1280,1300,1320,1340,1360,1380,1400,1420,1440,1460,1480,1500,1520,1540,1560,1580,1600,1620,1640,1660,1680],\"visibledatacolumn\":[150,277,436,572,704,846,920,994,1068,1142,1216,1290,1364,1438,1512,1586,1660,1734,1808,1882,1956,2030,2104,2178,2252,2326,2400,2474,2548,2622,2696,2770,2844,2918,2992,3066,3140,3214,3288,3362,3436,3510,3584,3658,3732,3806,3880,3954,4028,4102,4176,4250,4324,4398,4472,4546,4620,4694,4768,4842],\"ch_width\":4962,\"rh_height\":1760,\"luckysheet_selection_range\":[],\"zoomRatio\":1,\"dynamicArray\":[],\"images\":{},\"scrollLeft\":0,\"scrollTop\":0,\"calcChain\":[],\"filter_select\":null,\"filter\":null,\"luckysheet_conditionformat_save\":[],\"luckysheet_alternateformat_save\":[],\"dataVerification\":{},\"hyperlink\":{}},{\"name\":\"Data\",\"index\":\"sheet_02\",\"order\":1,\"status\":0,\"celldata\":[]},{\"name\":\"Picture\",\"index\":\"sheet_03\",\"order\":2,\"status\":0,\"celldata\":[]}]";
        JSONArray jsonArray = (JSONArray) JSONObject.parse(json);
        JSONObject jsonObject = (JSONObject) jsonArray.get(0);
        JSONArray jsonObjectList = jsonObject.getJSONArray("celldata");
        List groupList = new ArrayList();
        List selectList = new ArrayList();
        for (int i = 0; i < jsonObjectList.size(); i++) {
            String v=jsonObjectList.getJSONObject(i).get("v").toString();
            JSONObject jsonObject1 =JSONObject.parseObject(v);
            if(!StringUtils.isEmpty(jsonObject1.get("m"))){
                if(jsonObject1.get("m").toString().lastIndexOf("select")!= -1){
                    String s = jsonObject1.get("m").toString().substring(jsonObject1.get("m").toString().indexOf("(")+1,jsonObject1.get("m").toString().indexOf(")"));
                    selectList.add(s);
                }
                if(jsonObject1.get("m").toString().lastIndexOf("groupRight")!= -1){
                    String s = jsonObject1.get("m").toString().substring(jsonObject1.get("m").toString().indexOf("(")+1,jsonObject1.get("m").toString().indexOf(")"));
                    rightGroup(jsonObjectList.getJSONObject(i),maps,s,jsonObjectList);
                }
            }
        }
        System.out.println(groupList.toString());


        for (int i = 0; i <list2.size() ; i++) {
            String grade=list2.get(i).get("grade").toString();
            for (int j = 0; j < list3.size(); j++) {
                if(list3.get(j).get("grade").equals(grade)){
                    System.out.println("有匹配");
                    Map<String,Object> map =new HashMap<>();
                    map.put("name",list3.get(j).get("name"));
                    map.put("grade",list2.get(i).get("grade"));
                    listsum.add(map);
                }
            }
        }

        String[] x = new String[]{"grade","name"};
        for(int i=0;i<listsum.size();i++) {
            for (int j = 0; j < x.length; j++) {
                JSONObject jsonn = new JSONObject();
                V v1 = new V();
                jsonn.put("r",i);
                jsonn.put("c",j);
                jsonn.put("v",listsum.get(i).get(x[j]));
                Ct ct = new Ct();
                ct.setFa("General");
                ct.setT("g");
                v1.setCt(ct);
                listdaichuli.add(jsonn);
            }
        }

    }


    public static void mainuu(String[] args) throws SQLException, ClassNotFoundException {
        int xlast=0;
        int ylast=0;
        //获取数据

        String str =  HttpRequest.sendGet("http://api.jeecg.com/mock/26/subtotal",null);
        JSONObject jsonObjectStr  = JSONObject.parseObject(str);
        List<Map<String,Object>> list = (List<Map<String, Object>>) jsonObjectStr.get("data");

        //y轴最后一个格子的坐标为 表头所占+数据填充个数
        ylast=list.size()+2;
        //需要替换内容的
        List<JSONObject> listdaichuli= new ArrayList<>();
        List<JSONObject> groupList = new ArrayList<>();
        String json ="[{\"name\":\"Cell\",\"index\":\"sheet_01\",\"order\":0,\"status\":1,\"celldata\":[{\"r\":0,\"c\":0,\"v\":{\"v\":\"人员表1\",\"m\":\"人员表1\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"mc\":{\"r\":0,\"c\":0,\"rs\":1,\"cs\":4},\"ht\":\"0\"}},{\"r\":0,\"c\":1,\"v\":{\"mc\":{\"r\":0,\"c\":0},\"ht\":\"0\"}},{\"r\":0,\"c\":2,\"v\":{\"mc\":{\"r\":0,\"c\":0},\"ht\":\"0\"}},{\"r\":0,\"c\":3,\"v\":{\"mc\":{\"r\":0,\"c\":0},\"ht\":\"0\"}},{\"r\":1,\"c\":0,\"v\":{\"m\":\"姓名\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"姓名\"}},{\"r\":1,\"c\":1,\"v\":{\"m\":\"年级\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"年级\"}},{\"r\":1,\"c\":2,\"v\":{\"m\":\"分数\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"分数\"}},{\"r\":1,\"c\":3,\"v\":{\"m\":\"性别\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"性别\"}},{\"r\":2,\"c\":0,\"v\":{\"m\":\"select(name)\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"select(name)\"}},{\"r\":2,\"c\":1,\"v\":{\"m\":\"select(grade)\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"select(grade)\"}},{\"r\":2,\"c\":2,\"v\":{\"m\":\"select(fraction)\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"select(fraction)\"}},{\"r\":2,\"c\":3,\"v\":{\"m\":\"select(gender)\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"select(gender)\"}},{\"r\":2,\"c\":4,\"v\":{\"v\":\"2\",\"ct\":{\"fa\":\"General\",\"t\":\"n\"},\"m\":\"2\"}}],\"jfgird_select_save\":[],\"luckysheet_select_save\":[{\"row\":[3,3],\"column\":[4,4],\"row_focus\":3,\"column_focus\":4,\"moveXY\":{\"x\":3,\"y\":4},\"left\":296,\"width\":73,\"top\":60,\"height\":19,\"left_move\":296,\"width_move\":73,\"top_move\":60,\"height_move\":19}],\"config\":{\"merge\":{\"0_0\":{\"r\":0,\"c\":0,\"rs\":1,\"cs\":4}},\"borderInfo\":[{\"rangeType\":\"range\",\"borderType\":\"border-all\",\"color\":\"#000\",\"style\":\"1\",\"range\":[{\"left\":0,\"width\":295,\"top\":-1,\"height\":20,\"left_move\":0,\"width_move\":295,\"top_move\":0,\"height_move\":59,\"row\":[0,2],\"column\":[0,3],\"row_focus\":0,\"column_focus\":0}]}]},\"visibledatarow\":[20,40,60,80,100,120,140,160,180,200,220,240,260,280,300,320,340,360,380,400,420,440,460,480,500,520,540,560,580,600,620,640,660,680,700,720,740,760,780,800,820,840,860,880,900,920,940,960,980,1000,1020,1040,1060,1080,1100,1120,1140,1160,1180,1200,1220,1240,1260,1280,1300,1320,1340,1360,1380,1400,1420,1440,1460,1480,1500,1520,1540,1560,1580,1600,1620,1640,1660,1680],\"visibledatacolumn\":[74,148,222,296,370,444,518,592,666,740,814,888,962,1036,1110,1184,1258,1332,1406,1480,1554,1628,1702,1776,1850,1924,1998,2072,2146,2220,2294,2368,2442,2516,2590,2664,2738,2812,2886,2960,3034,3108,3182,3256,3330,3404,3478,3552,3626,3700,3774,3848,3922,3996,4070,4144,4218,4292,4366,4440],\"ch_width\":4560,\"rh_height\":1760,\"luckysheet_selection_range\":[],\"zoomRatio\":1},{\"name\":\"Data\",\"index\":\"sheet_02\",\"order\":1,\"status\":0,\"celldata\":[]},{\"name\":\"Picture\",\"index\":\"sheet_03\",\"order\":2,\"status\":0,\"celldata\":[]}]";
        String json2 ="[{\"name\":\"Cell\",\"index\":\"sheet_01\",\"order\":0,\"status\":1,\"celldata\":[{\"r\":0,\"c\":0,\"v\":{\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"ht\":\"0\",\"m\":\"区域\",\"v\":\"区域\",\"mc\":{\"r\":0,\"c\":0,\"rs\":2,\"cs\":1}}},{\"r\":0,\"c\":1,\"v\":{\"ht\":\"0\",\"m\":\"省份\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"省份\",\"mc\":{\"r\":0,\"c\":1,\"rs\":2,\"cs\":1}}},{\"r\":0,\"c\":3,\"v\":{\"mc\":{\"r\":0,\"c\":2}}},{\"r\":0,\"c\":4,\"v\":{\"mc\":{\"r\":0,\"c\":2}}},{\"r\":1,\"c\":0,\"v\":{\"mc\":{\"r\":0,\"c\":0}}},{\"r\":1,\"c\":1,\"v\":{\"mc\":{\"r\":0,\"c\":1}}},{\"r\":1,\"c\":2,\"v\":{\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"m\":\"单位\",\"v\":\"单位\"}},{\"r\":1,\"c\":3,\"v\":{\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"m\":\"金额\",\"v\":\"金额\"}},{\"r\":1,\"c\":4,\"v\":{\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"m\":\"小计\",\"v\":\"小计\"}},{\"r\":2,\"c\":0,\"v\":{\"m\":\"#groupt(areaname)\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"#groupt(areaname)\"}},{\"r\":2,\"c\":1,\"v\":{\"m\":\"#group(dept)\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"#group(dept)\"}},{\"r\":2,\"c\":2,\"v\":{\"m\":\"#select(price)\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"#select(price)\"}},{\"r\":2,\"c\":3,\"v\":{\"m\":\"#select(amount)\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"#select(amount)\"}},{\"r\":2,\"c\":4,\"v\":{\"m\":\"#select(price)\",\"ct\":{\"fa\":\"General\",\"t\":\"g\"},\"v\":\"#select(price)\"}}],\"jfgird_select_save\":[],\"luckysheet_select_save\":[{\"left\":436,\"width\":135,\"top\":120,\"height\":19,\"left_move\":436,\"width_move\":135,\"top_move\":120,\"height_move\":19,\"row\":[6,6],\"column\":[3,3],\"row_focus\":6,\"column_focus\":3}],\"config\":{\"merge\":{\"0_0\":{\"r\":0,\"c\":0,\"rs\":2,\"cs\":1},\"0_1\":{\"r\":0,\"c\":1,\"rs\":2,\"cs\":1},\"0_2\":{\"r\":0,\"c\":2,\"rs\":1,\"cs\":3}},\"borderInfo\":[{\"rangeType\":\"range\",\"borderType\":\"border-all\",\"style\":\"1\",\"color\":\"#000\",\"range\":[{\"row\":[0,1],\"column\":[0,3]}]},{\"rangeType\":\"range\",\"borderType\":\"border-all\",\"style\":\"1\",\"color\":\"#000\",\"range\":[{\"row\":[0,1],\"column\":[0,3]}]},{\"rangeType\":\"range\",\"borderType\":\"border-all\",\"style\":\"1\",\"color\":\"#000\",\"range\":[{\"row\":[0,1],\"column\":[0,3]}]},{\"rangeType\":\"range\",\"borderType\":\"border-none\",\"style\":\"1\",\"color\":\"#000\",\"range\":[{\"row\":[0,1],\"column\":[0,3]}]}],\"columnlen\":{\"0\":149,\"1\":126,\"2\":158,\"3\":135,\"4\":131,\"5\":141},\"customWidth\":{\"0\":1,\"1\":1,\"2\":1,\"3\":1,\"4\":1,\"5\":1},\"rowlen\":{},\"rowhidden\":{}},\"visibledatarow\":[20,40,60,80,100,120,140,160,180,200,220,240,260,280,300,320,340,360,380,400,420,440,460,480,500,520,540,560,580,600,620,640,660,680,700,720,740,760,780,800,820,840,860,880,900,920,940,960,980,1000,1020,1040,1060,1080,1100,1120,1140,1160,1180,1200,1220,1240,1260,1280,1300,1320,1340,1360,1380,1400,1420,1440,1460,1480,1500,1520,1540,1560,1580,1600,1620,1640,1660,1680],\"visibledatacolumn\":[150,277,436,572,704,846,920,994,1068,1142,1216,1290,1364,1438,1512,1586,1660,1734,1808,1882,1956,2030,2104,2178,2252,2326,2400,2474,2548,2622,2696,2770,2844,2918,2992,3066,3140,3214,3288,3362,3436,3510,3584,3658,3732,3806,3880,3954,4028,4102,4176,4250,4324,4398,4472,4546,4620,4694,4768,4842],\"ch_width\":4962,\"rh_height\":1760,\"luckysheet_selection_range\":[],\"zoomRatio\":1,\"dynamicArray\":[],\"images\":{},\"scrollLeft\":0,\"scrollTop\":0,\"calcChain\":[],\"filter_select\":null,\"filter\":null,\"luckysheet_conditionformat_save\":[],\"luckysheet_alternateformat_save\":[],\"dataVerification\":{},\"hyperlink\":{}},{\"name\":\"Data\",\"index\":\"sheet_02\",\"order\":1,\"status\":0,\"celldata\":[]},{\"name\":\"Picture\",\"index\":\"sheet_03\",\"order\":2,\"status\":0,\"celldata\":[]}]";

        JSONArray jsonArray = (JSONArray) JSONObject.parse(json2);
        //如果只有一个sheet那就是get(0),有多个那就对应取下标
        JSONObject jsonObject = (JSONObject) jsonArray.get(0);
        JSONArray jsonObjectList = jsonObject.getJSONArray("celldata");
        for(int i=0;i<jsonObjectList.size();i++) {
            String v=jsonObjectList.getJSONObject(i).get("v").toString();
            JSONObject jsonObject1 =JSONObject.parseObject(v);
            if(ObjectUtils.isNotEmpty(jsonObject1.get("m"))){
                if(jsonObject1.get("m").toString().lastIndexOf("select")!= -1  ){
                    JSONObject jsonn = new JSONObject();
                    V v1 = new V();
                    jsonn.put("r",jsonObjectList.getJSONObject(i).get("r"));
                    jsonn.put("c",jsonObjectList.getJSONObject(i).get("c"));
                    jsonn.put("v",jsonObject1);
                    v1.setM(jsonObject1.get("m").toString());
                    Ct ct = new Ct();
                    ct.setFa("General");
                    ct.setT("g");
                    v1.setCt(ct);
                    listdaichuli.add(jsonn);
                }
                if( jsonObject1.get("m").toString().lastIndexOf("group")!= -1){
                    JSONObject jsonn = new JSONObject();
                    V v1 = new V();
                    jsonn.put("r",jsonObjectList.getJSONObject(i).get("r"));
                    jsonn.put("c",jsonObjectList.getJSONObject(i).get("c"));
                    jsonn.put("v",jsonObject1);
                    v1.setM(jsonObject1.get("m").toString());
                    Ct ct = new Ct();
                    ct.setFa("General");
                    ct.setT("g");
                    v1.setCt(ct);
                    groupList.add(jsonn);
                }
            }
        }

        /*数据分组*/
        for (int i = 0; i < groupList.size(); i++) {
            String v=groupList.get(i).get("v").toString();
            JSONObject jsonObject1 =JSONObject.parseObject(v);
            String sel=jsonObject1.get("m").toString();
            String ghj=sel.substring(sel.indexOf("(")+1,sel.indexOf(")"));
            Map<Object, List<Map<String, Object>>> areaname = list.stream().collect(Collectors.groupingBy(c -> c.get(ghj)));
            System.out.println(areaname);
        }


        JSONArray jsonObjectListn = new JSONArray();
        for (int i = 0; i < list.size(); i++) {
            for (int j = 0; j < listdaichuli.size(); j++) {
                String v=listdaichuli.get(j).get("v").toString();
                JSONObject jsonObject1 =JSONObject.parseObject(v);
                String sel=jsonObject1.get("m").toString();
                String ghj=sel.substring(sel.indexOf("(")+1,sel.indexOf(")"));
                JSONObject json1 = new JSONObject();
                V v2 = new V();
                json1.put("r",i+2);
                json1.put("c",j);
                json1.put("v",v2);
                v2.setM(list.get(i).get(ghj).toString());
                v2.setV(list.get(i).get(ghj).toString());
                Ct ct = new Ct();
                ct.setFa("General");
                ct.setT("g");
                v2.setCt(ct);
                jsonObjectList.add(json1);
            }
        }

        //设置边框
        Configuration conf = Configuration.defaultConfiguration();
        String set = JsonPath.using(conf).parse(jsonObject).set("$['config']['borderInfo'][0]['range'][0]['column'][1]", 10).jsonString();

        JSONObject jsonObjectconfig = jsonObject.getJSONObject("config");
        String borderInfo=jsonObjectconfig.get("borderInfo").toString();
        JSONArray jsonObject1 =JSONArray.parseArray(borderInfo);

        JSONArray jsonArray1=(JSONArray) jsonObject1.getJSONObject(0).get("range");

        JSONArray jsonArray2=(JSONArray) jsonArray1.getJSONObject(0).get("column"); //x轴边框
        JSONArray jsonArray3=(JSONArray) jsonArray1.getJSONObject(0).get("row");//y轴边框

        Map<String, Object> jsonMap = JSONObject.toJavaObject(jsonArray1.getJSONObject(0), Map.class);
        Integer[] a = {0,xlast};
        String set123 = JsonPath.using(conf).parse(jsonObject).set("$['config']['borderInfo'][0]['range'][0]['column']", a).jsonString();
        jsonMap.put("column",a);
        Integer[] b = {0,ylast};
        jsonMap.put("row",b);
        String jsonty = JSON.toJSONString(jsonMap);//map转String
        JSONObject jsonObject2gg=jsonObject1.getJSONObject(0);
        jsonObject1.set(0,JSON.parseObject(jsonty));//String转json
        jsonObject.put("config",jsonObjectconfig);
//        JSONObject json1 = new JSONObject();
//        V v = new V();
//        json1.put("r","2");
//        json1.put("c","4");
//        json1.put("v",v);
//        v.setM("测试");
//        Ct ct = new Ct();
//        ct.setFa("General");
//        ct.setT("g");
//        v.setCt(ct);
//        jsonObjectList.add(json1);
        jsonObject.put("celldata",jsonObjectList);
        jsonArray.add(jsonObject);
        String g=jsonArray.toString();
        System.out.println(jsonArray);
    }



    /**
     *横向扩展单元格
     * @Param jsonObject 单元格属性
     * @Param map  数据
     * @Param str  表头属性
     * */

    public static Map<String,Object> rightGroup(JSONObject jsonObject,List<Map<String,Object>>map,String str,JSONArray jsonArray){
        Map<String,Object> maprtn = new HashMap<>();
        Integer c = (Integer) jsonObject.get("c");
        Integer r = (Integer) jsonObject.get("r");
        JSONObject v = (JSONObject) jsonObject.get("v");
        String  value = v.get("v").toString();
        JSONObject mc = (JSONObject) v.get("mc");
        Integer cs  = (Integer) mc.get("cs");
        Integer rs  = (Integer) mc.get("rs");
        List<JSONObject> cellDataList = new ArrayList<>();
        JSONObject mergejsonObject = new JSONObject();
        List  staticlist = new ArrayList();
        staticHeader(jsonArray,cs,c,r,staticlist);
        int num=0;
        for (int i = 0; i <map.size(); i++) {
            /*循环表头*/
            /*组装celldata 数据*/
            Map<String,Object> map1 = map.get(i);
            JSONObject celljsonObject = new JSONObject();
            celljsonObject.put("r",r);
            celljsonObject.put("c",c+(i*cs));
            celljsonObject.put("v",map1.get(str));
            cellDataList.add(celljsonObject);
                for (int k = 0; k < staticlist.size(); k++) {


                    List  list = (List) staticlist.get(k);
                    for (int l = 0; l <list.size(); l++) {
                        JSONObject jsonObject11 = new JSONObject();
                        jsonObject11 =  JSONObject.parseObject(list.get(l).toString());
                        jsonObject11.put("c",c+(i*cs)+k);
                        cellDataList.add(jsonObject11);
                    }
                }


            /*组装合并单元格数据*/
            String key = r+"_"+(c+(i*cs));
            JSONObject jsonObject1 = new JSONObject();
            jsonObject1.put("r",r);
            jsonObject1.put("c",c+(i*cs));
            jsonObject1.put("rs",rs) ;
            jsonObject1.put("cs",cs);
            mergejsonObject.put(key,jsonObject1);
        }
        System.out.println("num="+num);
        System.out.println(cellDataList.toString());
        System.out.println("-----------------------");
        System.out.println(mergejsonObject.toString());
        maprtn.put("cell",cellDataList);
        maprtn.put("merge",mergejsonObject);
        return maprtn;
    }
//    获取指定列的数据
    public static List<JSONObject> getlie(List<Map<String,Object>>map,JSONArray jsonArray,String[] c){
        List<JSONObject> list=new ArrayList();
        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject gh= (JSONObject)jsonArray.get(i);
            for (int j = 0; j < c.length; j++) {
                if(gh.get("c").equals(Integer.valueOf(c[j]))){
                    list.add((JSONObject)jsonArray.get(i));
                }
            }

        }
        return list;
    }

    /*获取动态表头下的静态表头*/
    public static  void staticHeader(JSONArray jsonArray,Integer size,Integer c,Integer r,List list){
        for (int j = 0; j < size; j++) {
            List list1 = new ArrayList();
            Integer  i1 = 0 ;
            while(true){
                i1++;
                boolean  b  = true;
                for (int k = 0; k < jsonArray.size(); k++) {
                    JSONObject jsonObject1 = (JSONObject) jsonArray.get(k);
                    //循环数据 找到横向group下得静态属性
                    if ((r + i1) == (Integer) jsonObject1.get("r") && (Integer)jsonObject1.get("c") == (c+j)) {
                        list1.add(jsonObject1);
                        b =false;
                        break;
                    }
                }
                if(b){
                    list.add(list1);
                    break;
                }
                else{
                    continue;
                }
            }
        }
    }



}
